import csv
import pandas as pd
from tqdm import tqdm
import numpy as np
import os
from sklearn import preprocessing


def read_data(file_path):
    # file_path = "G:\\粤港澳数模\\附件：相关股票数据\\000028.SZ.xlsx"
    df_file_data = pd.read_excel(file_path)
    return df_file_data


def cal_num_day(df_file_data, stook_name):
    df_day_num = df_file_data[['时间', '成交量（手）']]
    # print(day_num)
    num_sum = 0
    day_sum = 0
    num_list = []
    day_start = str(df_day_num.iloc[0, 0])[0:8]
    day_end = ''
    temp = 0
    for index, row in tqdm(df_day_num.iterrows()):
        day_end = str(row['时间'])[0:8]
        if day_end == day_start:
            temp = int(row['成交量（手）'])
            continue
        else:
            day_start = day_end
            num_sum += temp
            day_sum += 1
            num_list.append(temp)
            # print(num_sum,day_sum)
    num_sum += int(df_day_num.tail(n=1)['成交量（手）'])
    day_sum += 1
    num_list.append(int(df_day_num.tail(n=1)['成交量（手）']))
    print(stook_name,
          '每日交易量（手）平均值{}，方差为{}，标准差为{}\n'.format(np.mean(num_list), np.var(num_list), np.std(num_list)))
    return np.mean(num_list), np.var(num_list), np.std(num_list)


def cal_sum_day(df_file_data, stook_name):
    df_day_sum = df_file_data[['时间', '成交额（元）']]
    # print(day_sum)
    sum_sum = 0.0
    day_sum = 0
    sum_list = []
    day_start = str(df_day_sum.iloc[0, 0])[0:8]
    day_end = ''
    temp = 0.0
    for index, row in tqdm(df_day_sum.iterrows()):
        day_end = str(row['时间'])[0:8]
        if day_end == day_start:
            temp = float(row['成交额（元）'])
            continue
        else:
            day_start = day_end
            sum_sum += temp
            day_sum += 1
            # print(sum_sum,day_sum)
            sum_list.append(temp)
    sum_sum += float(df_day_sum.tail(n=1)['成交额（元）'])
    day_sum += 1
    sum_list.append(float(df_day_sum.tail(n=1)['成交额（元）']))
    # print(stook_name, '平均日交易额（元）：', sum_sum/day_sum, '\n')
    print(stook_name,
          '每日交易额（元）平均值{}，方差为{}，标准差为{}\n'.format(np.mean(sum_list), np.var(sum_list), np.std(sum_list)))
    return np.mean(sum_list), np.var(sum_list), np.std(sum_list)


def cal_num_5min(df_file_data, stook_name):
    df_num_5min = df_file_data[['时间', '成交量（手）']]
    min_start = int(str(df_num_5min.iloc[0, 0])[4:12])
    num_start = 0
    num_per5min = {}
    num_list_per5min = []
    # flag = df_num_5min.shape[0]
    for index, row in tqdm(df_num_5min.iterrows()):
        min_end = int(str(row['时间'])[4:12])
        # print(min_start,min_end)
        if (min_end - min_start) % 5 == 0 and min_end not in num_per5min and str(min_end)[4:8] != '1300' and int(
                str(min_end)[4:8]) >= 925 and int(str(min_end)[4:8]) <= 1500:
            # if int(str(df_num_5min.loc[index]['时间'])[8:12]) > 1500:
            #     num_end = int(row['成交量（手）'])
            #     num_per5min[min_end] = num_end - num_start
            #     num_start = 0
            #     continue
            num_end = int(row['成交量（手）'])
            num_per5min[min_end] = num_end - num_start
            # print(min_end, num_start, num_end)
            num_start = num_end
            min_start = min_end
        if int(str(row['时间'])[8:12]) == 1500:
            num_start = 0
    # print(num_per5min)
    sum = 0
    for i in num_per5min:
        num_list_per5min.append(num_per5min[i])
        sum += num_per5min[i]
        # print(sum)
    # print(len(num_list_per5min))
    # print(num_list_per5min)
    avg_num_per5min = np.mean(num_list_per5min)
    var_num_per5min = np.var(num_list_per5min)
    std_num_per5min = np.std(num_list_per5min, ddof=1)
    print(stook_name,
          '以五分钟为间隔的成交量（手）平均值为{}，方差为{}，标准差为{}\n'.format(avg_num_per5min, var_num_per5min,
                                                                              std_num_per5min))
    return avg_num_per5min, var_num_per5min, std_num_per5min


def cal_sum_5min(df_file_data, stook_name):
    df_sum_5min = df_file_data[['时间', '成交额（元）']]
    min_start = int(str(df_sum_5min.iloc[0, 0])[4:12])
    sum_start = 0
    sum_per5min = {}
    sum_list_per5min = []
    for index, row in tqdm(df_sum_5min.iterrows()):
        min_end = int(str(row['时间'])[4:12])
        # print(min_start,min_end)
        if (min_end - min_start) % 5 == 0 and min_end not in sum_per5min and str(min_end)[4:8] != '1300' and str(
                min_end)[4:8] != '0915' and str(min_end)[4:8] != '0920':
            sum_end = int(row['成交额（元）'])
            sum_per5min[min_end] = sum_end - sum_start
            # print(min_end, num_start, num_end)
            sum_start = sum_end
            min_start = min_end
        if int(str(row['时间'])[8:12]) == 1500:
            sum_start = 0
    # print(sum_per5min)
    sum = 0
    for i in sum_per5min:
        sum_list_per5min.append(sum_per5min[i])
        sum += sum_per5min[i]
        # print(sum)
    # print(len(sum_list_per5min))
    # print(num_list_per5min)
    avg_num_per5min = np.mean(sum_list_per5min)
    var_num_per5min = np.var(sum_list_per5min)
    std_num_per5min = np.std(sum_list_per5min, ddof=1)
    print(stook_name,
          '以五分钟为间隔的成交额（元）平均值为{}，方差为{}，标准差为{}\n'.format(avg_num_per5min, var_num_per5min,
                                                                              std_num_per5min))
    return avg_num_per5min, var_num_per5min, std_num_per5min


def cal_high_low_distance_permin(df_file_data, stook_name):
    df_high_low_distance = df_file_data[['时间', '收盘价', '最高价', '最低价']]
    distance_dic = {}
    distance_list = []
    for index, row in tqdm(df_high_low_distance.iterrows()):
        min_end = str(row['时间'])[4:12]
        if int(min_end[4:8]) >= 925:
            distance_dic[min_end] = float((row['最高价'] - row['最低价']) * 100 / row['收盘价'])  # 算的是百分比
    for i in distance_dic:
        distance_list.append(distance_dic[i])
    # print(len(distance_list))
    avg_high_low_distance_permin = np.mean(distance_list)
    var_high_low_distance_permin = np.var(distance_list)
    std_high_low_distance_permin = np.std(distance_list, ddof=1)
    print(stook_name, '以一分钟为间隔的最高价和最低价之间振幅的平均值为{}，方差为{}，标准差为{}\n'.format(
        avg_high_low_distance_permin, var_high_low_distance_permin, std_high_low_distance_permin))
    return avg_high_low_distance_permin, var_high_low_distance_permin, std_high_low_distance_permin


if __name__ == "__main__":
    stock_dic = {'002027': '分众传媒', '000069': '华侨城Ａ', '002233': '塔牌集团', '300014': '亿纬锂能',
                 '600383': '金地集团', '002060': '粤水电',
                 '002475': '立讯精密', '600048': '保利地产', '002352': '顺丰控股', '000636': '风华高科',
                 '001914': '招商积余', '002511': '中顺洁柔',
                 '002449': '国星光电', '601318': '中国平安', '002303': '美盈森', '600183': '生益科技',
                 '600323': '瀚蓝环境', '002461': '珠江啤酒',
                 '000049': '德赛电池', '002152': '广电运通', '600872': '中炬高新', '002138': '顺络电子',
                 '000921': '海信家电', '600332': '白云山',
                 '300115': '长盈精密', '002035': '华帝股份', '000513': '丽珠集团', '600325': '华发股份',
                 '000651': '格力电器', '000028': '国药一致',
                 '601788': '光大证券', '000333': '美的集团', '002594': '比亚迪'
                 }
    mean_num_perday_list = []
    var_num_perday_list = []
    std_num_perday_list = []
    mean_sum_perday_lsit = []
    var_sum_perday_list = []
    std_sum_perday_list = []
    mean_num_per5min_list = []
    var_num_per5min_list = []
    std_num_per5min_list = []
    mean_sum_per5min_list = []
    var_sum_per5min_list = []
    std_sum_per5min_list = []
    mean_high_low_distance_permin_list = []
    var_high_low_distance_permin_list = []
    std_high_low_distance_permin_list = []

    stock_name_list = []

    # file_name_list = ["300014.SZ.xlsx"]
    file_name_list = os.listdir('G:\\粤港澳数模\\附件：相关股票数据')
    print(len(file_name_list))
    for i in file_name_list:
        stock_name = stock_dic[i[0:6]] + i[0:9]
        print(stock_name)
        stock_name_list.append(stock_name)
        file_path = 'G:\\粤港澳数模\\附件：相关股票数据\\' + i
        df_file_data = read_data(file_path)

        a, b, c = cal_num_day(df_file_data, stock_name)
        x, y, z = cal_sum_day(df_file_data, stock_name)
        m, n, k = cal_num_5min(df_file_data, stock_name)
        o, p, q = cal_sum_5min(df_file_data, stock_name)
        d, e, f = cal_high_low_distance_permin(df_file_data, stock_name)

        mean_num_perday_list.append(a)
        var_num_perday_list.append(b)
        std_num_perday_list.append(c)

        mean_sum_perday_lsit.append(x)
        var_sum_perday_list.append(y)
        std_sum_perday_list.append(z)

        mean_num_per5min_list.append(m)
        var_num_per5min_list.append(n)
        std_num_per5min_list.append(k)

        mean_sum_per5min_list.append(o)
        var_sum_per5min_list.append(p)
        std_sum_per5min_list.append(q)

        mean_high_low_distance_permin_list.append(d)
        var_high_low_distance_permin_list.append(e)
        std_high_low_distance_permin_list.append(f)

        print('\n')

    maxmin_scaler = preprocessing.MinMaxScaler()
    a_maxmin = maxmin_scaler.fit_transform(np.array(mean_num_perday_list).reshape(-1, 1))
    # print(a_maxmin, len(a_maxmin))
    a_maxmin_list = []
    for i in a_maxmin:
        a_maxmin_list.append(float(i))
    print('mean_num_perday', a_maxmin_list)

    # min_max_scaler = preprocessing.MinMaxScaler()
    # x_minmax = min_max_scaler.fit_transform(np.array(mean_num_perday_list).reshape(-1, 1))
    # print(x_minmax, len(x_maxmin))
    # maxmin_scaler = preprocessing.MinMaxScaler()
    # b_maxmin = maxmin_scaler.fit_transform(np.array(var_num_perday_list).reshape(-1, 1))
    # print(b_maxmin, len(b_maxmin))

    maxmin_scaler = preprocessing.MinMaxScaler()
    c_maxmin = maxmin_scaler.fit_transform(np.array(std_num_perday_list).reshape(-1, 1))
    # print(c_maxmin, len(c_maxmin))
    c_maxmin_list = []
    for i in c_maxmin:
        c_maxmin_list.append(float(i))
    print('std_num_perday', c_maxmin_list)

    maxmin_scaler = preprocessing.MinMaxScaler()
    x_maxmin = maxmin_scaler.fit_transform(np.array(mean_sum_perday_lsit).reshape(-1, 1))
    # print(x_maxmin, len(x_maxmin))
    x_maxmin_list = []
    for i in x_maxmin:
        x_maxmin_list.append(float(i))
    print('mean_sum_perday', x_maxmin_list)

    # maxmin_scaler = preprocessing.MinMaxScaler()
    # y_maxmin = maxmin_scaler.fit_transform(np.array(var_sum_perday_list).reshape(-1, 1))
    # print(y_maxmin, len(y_maxmin))

    maxmin_scaler = preprocessing.MinMaxScaler()
    z_maxmin = maxmin_scaler.fit_transform(np.array(std_sum_perday_list).reshape(-1, 1))
    # print(z_maxmin, len(z_maxmin))
    z_maxmin_list = []
    for i in z_maxmin:
        z_maxmin_list.append(float(i))
    print('std_sum_perday', z_maxmin_list)

    maxmin_scaler = preprocessing.MinMaxScaler()
    m_maxmin = maxmin_scaler.fit_transform(np.array(mean_num_per5min_list).reshape(-1, 1))
    # print(m_maxmin, len(m_maxmin))
    m_maxmin_list = []
    for i in m_maxmin:
        m_maxmin_list.append(float(i))
    print('mean_num_per5min', m_maxmin_list)

    # maxmin_scaler = preprocessing.MinMaxScaler()
    # n_maxmin = maxmin_scaler.fit_transform(np.array(var_num_per5min_list).reshape(-1, 1))
    # print(n_maxmin, len(n_maxmin))

    maxmin_scaler = preprocessing.MinMaxScaler()
    k_maxmin = maxmin_scaler.fit_transform(np.array(std_num_per5min_list).reshape(-1, 1))
    # print(k_maxmin, len(k_maxmin))
    k_maxmin_list = []
    for i in k_maxmin:
        k_maxmin_list.append(float(i))
    print('std_num_per5min', k_maxmin_list)

    maxmin_scaler = preprocessing.MinMaxScaler()
    o_maxmin = maxmin_scaler.fit_transform(np.array(mean_sum_per5min_list).reshape(-1, 1))
    # print(o_maxmin, len(o_maxmin))
    o_maxmin_list = []
    for i in o_maxmin:
        o_maxmin_list.append(float(i))
    print('mean_sum_per5min', o_maxmin_list)

    # maxmin_scaler = preprocessing.MinMaxScaler()
    # p_maxmin = maxmin_scaler.fit_transform(np.array(var_sum_per5min_list).reshape(-1, 1))
    # print(p_maxmin, len(p_maxmin))

    maxmin_scaler = preprocessing.MinMaxScaler()
    q_maxmin = maxmin_scaler.fit_transform(np.array(std_sum_per5min_list).reshape(-1, 1))
    # print(q_maxmin, len(q_maxmin))
    q_maxmin_list = []
    for i in q_maxmin:
        q_maxmin_list.append(float(i))
    print('std_sum_per5min', q_maxmin_list)

    maxmin_scaler = preprocessing.MinMaxScaler()
    d_maxmin = maxmin_scaler.fit_transform(np.array(mean_high_low_distance_permin_list).reshape(-1, 1))
    # print(d_maxmin, len(d_maxmin))
    d_maxmin_list = []
    for i in d_maxmin:
        d_maxmin_list.append(float(i))
    print('mean_high_low_distance_permin', d_maxmin_list)

    # maxmin_scaler = preprocessing.MinMaxScaler()
    # e_maxmin = maxmin_scaler.fit_transform(np.array(var_high_low_distance_permin_list).reshape(-1, 1))
    # print(e_maxmin, len(e_maxmin))

    maxmin_scaler = preprocessing.MinMaxScaler()
    f_maxmin = maxmin_scaler.fit_transform(np.array(std_high_low_distance_permin_list).reshape(-1, 1))
    # print(f_maxmin, len(f_maxmin))
    f_maxmin_list = []
    for i in f_maxmin:
        f_maxmin_list.append(float(i))
    print('std_high_low_distance_permin', f_maxmin_list)

    dataframe = pd.DataFrame({'mean_num_perday': a_maxmin_list, 'std_num_perday': c_maxmin_list,
                              'mean_sum_perday': x_maxmin_list, 'std_sum_perday': z_maxmin_list,
                              'mean_num_per5min': m_maxmin_list, 'std_num_per5min': k_maxmin_list,
                              'mean_sum_per5min': o_maxmin_list, 'std_sum_per5min': q_maxmin_list,
                              'mean_high_low_distance_permin': d_maxmin_list,
                              'std_high_low_distance_permin': f_maxmin_list
                              })
    dataframe.index = stock_name_list
    dataframe.to_csv("G:\\粤港澳数模\\归一化之后的数.csv")
